﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using AtletIS.Shared.Helpers;
using AtletIS.Shared.Data.aDbDataSetTableAdapters;
using AtletIS.Shared.Data;

namespace AtletIS.Shared.Forms.Signups
{
    public partial class frmSignupsImport : Form
    {
        private PerformancesTableAdapter taPerformances = new PerformancesTableAdapter();
        private DataTable dtImportData = new DataTable();
        private string file = string.Empty;
        private OleDbConnection oleConn;
        private OleDbDataAdapter oleAdapter;
        
        // Constructor
        public frmSignupsImport()
        {
            InitializeComponent();
            this.Icon = Properties.Resources.AtletIS64;
        }

        // Form Load
        private void frmSignupsImport_Load(object sender, EventArgs e)
        {
            lblTitle.Text = Globals.ActiveCompetition.Name.ToString();
            DisciplineListHelper.FillTreeView(tvDisciplines);
        }

        // Vybrat subor
        private void tsbOpenFile_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Spĺňa súbor pre import nasledovné podmienky:" + Environment.NewLine + 
                " - prvý riadok obsahuje hlavičku (názvy stĺpcov)" + Environment.NewLine + 
                " - stĺpec: MENO        formát: TEXT" + Environment.NewLine +
                " - stĺpec: PRIEZVISKO  formát: TEXT" + Environment.NewLine +
                " - stĺpec: DAT_NAR     formát: DATUM" + Environment.NewLine +
                " - stĺpec: KLUB        formát: TEXT" + Environment.NewLine +
                " - stĺpec: KRAJINA     formát: TEXT" + Environment.NewLine + 
                " - stĺpec: ST_CISLO    formát: CISLO" + Environment.NewLine +
                " - stĺpec: SMS         formát: TEXT" + Environment.NewLine +
                " - stĺpec: SB         formát: TEXT" + Environment.NewLine +
                " - stĺpec: PB         formát: TEXT" + Environment.NewLine,
                "Import prihlášok", 
                MessageBoxButtons.YesNo, 
                MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
                return;

            OpenFileDialog openDialog = new OpenFileDialog();
            openDialog.Title = "Vyberte súbor pre import";
            openDialog.Filter = "Súbory programu Excel|*.xls;*.xlsx";

            if (openDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                file = openDialog.FileName;
                tsslFile.Text = "Súbor: " + file;

                try
                {
                    oleConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + file + "'; Extended Properties=Excel 8.0;");
                    oleConn.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + Environment.NewLine + ex.StackTrace, "Import", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                try
                {
                    DataTable dtSchema = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, null });
                    if (dtSchema != null)
                    {
                        cmbSheets.DataSource = dtSchema;
                        cmbSheets.DisplayMember = "TABLE_NAME";
                        cmbSheets.ValueMember = "TABLE_NAME";
                        
                        cmbSheets.Enabled = true;

                        cmbSheets_SelectedIndexChanged(null, null);
                    }
                    else
                    {
                        MessageBox.Show("Súbor neobsahuje žiadne hárky.", "Import", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        return;
                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + Environment.NewLine + ex.StackTrace, "Import", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
        }

        // Zmena harku
        private void cmbSheets_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbSheets.Enabled == true)
            {
                try
                {
                    dtImportData.Rows.Clear();
                    oleAdapter = new OleDbDataAdapter(string.Format("SELECT MENO, PRIEZVISKO, DAT_NAR, KLUB, KRAJINA, ST_CISLO, SMS, SB, PB FROM [{0}]", cmbSheets.SelectedValue.ToString()), oleConn);
                    oleAdapter.Fill(dtImportData);

                    dgvImport.DataSource = null;
                    dgvImport.DataSource = dtImportData;
                    dgvImport.ClearSelection();
                }
                catch
                {
                    MessageBox.Show("Hárok neobsahuje požadované stĺpce.", "Import", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    dgvImport.DataSource = null;
                    return;
                }
            }
        }

        // Importovat
        private void tsbImport_Click(object sender, EventArgs e)
        {
            try
            {
                // Kontroly
                if (dgvImport.RowCount == 0)
                {
                    MessageBox.Show("Neexistujú data pre import.", "Import");
                    return;
                }

                bool bDiscipline = false;
                foreach (TreeNode tn in tvDisciplines.Nodes)
                {
                    if (tn.Checked)
                    {
                        bDiscipline = true;
                        break;
                    }
                }

                if (bDiscipline == false)
                {
                    MessageBox.Show("Nevybrali ste žiadnu disciplínu.", "Import");
                    return;
                }

                int disciplines = 0;
                if(CheckColumns(dgvImport))
                {
                    // Ukladanie
                    foreach (TreeNode tn in tvDisciplines.Nodes)
                    {
                        if (tn.Checked)
                        {
                            int disciplineId = Convert.ToInt32(tn.Name);
                            DataTable dtPerformances = DatabaseHelper.GetPerformancesByDisciplineListId(disciplineId); // taPerformances.GetByDisciplineListId(disciplineId);

                            foreach (DataRow dr in dtImportData.Rows)
                            {
                                int nSb = 0, nPb = 0;
                                
                                if (dr["ST_CISLO"] == DBNull.Value)
                                    dr["ST_CISLO"] = 0;

                                if (dr["KRAJINA"] == DBNull.Value)
                                    dr["KRAJINA"] = "SVK";

                                try
                                {
                                    DateTime birthDate = Convert.ToDateTime(dr["DAT_NAR"]);
                                }
                                catch
                                {
                                    MessageBox.Show("Dátum nie je v správnom formáte!" + Environment.NewLine + dr["MENO"] + " " + dr["PRIEZVISKO"], "Import");
                                    return;
                                }

                                try
                                {
                                    // SB
                                    if (dr["SB"] != DBNull.Value)
                                    {
                                        if (rbFormatTime.Checked)
                                        {
                                            TimeSpan tsSB = TimeSpan.Parse(dr["SB"].ToString());
                                            nSb = tsSB.Milliseconds / 10 + tsSB.Seconds * 100 + tsSB.Minutes * 100 * 100 + tsSB.Hours * 100 * 100 * 100;
                                        }

                                        if (rbFormatNumber.Checked)
                                            nSb = Convert.ToInt32(dr["SB"]);
                                    }

                                    // PB
                                    if (dr["PB"] != DBNull.Value)
                                    {
                                        if (rbFormatTime.Checked)
                                        {
                                            TimeSpan tsPB = TimeSpan.Parse(dr["PB"].ToString());
                                            nPb = tsPB.Milliseconds / 10 + tsPB.Seconds * 100 + tsPB.Minutes * 100 * 100 + tsPB.Hours * 100 * 100 * 100;
                                        }

                                        if (rbFormatNumber.Checked)
                                            nPb = Convert.ToInt32(dr["PB"]);
                                    }
                                }
                                catch { }

                                AtletIS.Shared.Data.aDbDataSet.PerformancesRow drPerformance = this.aDbDataSet.Performances.NewPerformancesRow(); 
                                drPerformance.DisciplineListId = disciplineId;
                                drPerformance.CompetitorId = 1; // FIKT
                                drPerformance.FirstName = dr["MENO"].ToString().Trim();
                                drPerformance.Surname = dr["PRIEZVISKO"].ToString().Trim();
                                drPerformance.Birthdate = Convert.ToDateTime(dr["DAT_NAR"]);
                                drPerformance.Club = dr["KLUB"].ToString().Trim();
                                drPerformance.CountryId = dr["KRAJINA"].ToString().Trim();
                                drPerformance.SMS = string.Empty;
                                drPerformance.StartNumber = Convert.ToInt32(dr["ST_CISLO"]);
                                drPerformance.Lane = 0;
                                drPerformance.Code = 0;
                                drPerformance.SB = 0;   // Lokalne prihlaseni pretekari nemaju SB, PB zo statistiky
                                drPerformance.PB = 0;
                                drPerformance.Result = 0;
                                drPerformance.ResultQuality = string.Empty;
                                drPerformance.AdvResult = 0;
                                drPerformance.Rank = 0;
                                drPerformance.SecondaryRank = 0;
                                drPerformance.Reaction = string.Empty;
                                drPerformance.Points = 0;
                                drPerformance.PointsScoring = true;
                                drPerformance.Active = true;
                                drPerformance.Sync = false;
                                drPerformance.WebId = 0;
                                drPerformance.Modified = DateTime.Now;
                                drPerformance.SetRealStartTimeNull();

                                this.aDbDataSet.Performances.AddPerformancesRow(drPerformance);
                                this.taPerformances.Update(this.aDbDataSet.Performances);
                            }

                            tn.Checked = false;
                            disciplines += 1;
                        }
                    }
                    MessageBox.Show(string.Format("Import do {0} disciplín prebehol úspešne.", disciplines.ToString()),"Import", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + Environment.NewLine + ex.StackTrace, "Import", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
        }

        // Kontrola stlpcov
        private bool CheckColumns(DataGridView dgv)
        {
            if (dgv.Columns["MENO"].ValueType.FullName != "System.String")
            {
                MessageBox.Show("Stĺpec MENO nemá správny formát.", "Import");
                return false;
            }

            if (dgv.Columns["PRIEZVISKO"].ValueType.FullName != "System.String")
            {
                MessageBox.Show("Stĺpec PRIEZVISKO nemá správny formát.", "Import");
                return false;
            }

            if (dgv.Columns["DAT_NAR"].ValueType.FullName != "System.DateTime")
            {
                MessageBox.Show("Stĺpec DAT_NAR nemá správny formát.", "Import");
                return false;
            }
            
            if (dgv.Columns["KLUB"].ValueType.FullName != "System.String")
            {
                MessageBox.Show("Stĺpec KLUB nemá správny formát.", "Import");
                return false;
            }

            if (dgv.Columns["KRAJINA"].ValueType.FullName != "System.String")
            {
                MessageBox.Show("Stĺpec KRAJINA nemá správny formát.", "Import");
                return false;
            }

            if (dgv.Columns["ST_CISLO"].ValueType.FullName != "System.Double")
            {
                MessageBox.Show("Stĺpec ST_CISLO nemá správny formát.", "Import");
                return false;
            }

            if (dgv.Columns["SMS"].ValueType.FullName != "System.String")
            {
                MessageBox.Show("Stĺpec SMS nemá správny formát.", "Import");
                return false;
            }
            
            return true;
        }

        // Form Closing
        private void frmSignupsImport_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (oleConn != null)
            {
                if (oleConn.State == ConnectionState.Open)
                {
                    oleConn.Close();
                    oleConn.Dispose();
                }
            }
        }






    }
}
